﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data.SqlClient;
using System.Data;
namespace DAO
{
    public class HoaDonDAO
    {
        public int ThemHoaDon(HoaDonDTO hdDTO)
        {
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            string sql = "INSERT INTO HoaDon VALUES(" + hdDTO.MaHD + ", '" + hdDTO.GioVao + "', '" + hdDTO.GioRa + "', '" + hdDTO.TenUser + "', N'" + hdDTO.TinhTrangHD + "', " + hdDTO.ThanhTien + ")";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandText = sql;
            cmd.ExecuteNonQuery();
            conn.Close();
            int kq = 1; ;
            if (kq >= 0)
                return 1;
            else
                return 0;
        }
        public int CapNhatHoaDon(HoaDonDTO hdDTO)
        {
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            string sql = "Update HoaDon set TenUser='" + hdDTO.TenUser + "', ThanhTien = " + hdDTO.ThanhTien;
            string dk =" where MaHD="+hdDTO.MaHD;
            if (hdDTO.GioRa != "")
            {
                sql += ",GioRa='" + hdDTO.GioRa + "'";
            }
            if (hdDTO.TinhTrangHD != "")
            {
                sql += ",TinhTrangHD=N'" + hdDTO.TinhTrangHD + "'";
            }
            sql += dk;
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandText = sql;
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            if (kq >= 0)
                return 1;
            else
                return 0;
        }
        public int XoaHoaDon(int MaHD)
        {
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            string sql = "Delete HoaDon where MaHD=" + MaHD;
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandText = sql;
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            if (kq >= 0)
                return 1;
            else
                return 0;
        }
        public int LayMaHoaDon()
        {
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            string sql = "select top 1 MaHD from HoaDon order by MaHD desc";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandText = sql;
            int kq;
            try
            {
                kq = int.Parse(cmd.ExecuteScalar().ToString());
            }
            catch
            {
                conn.Close();
                return 0;
            }
            conn.Close();
            return kq;
        }
        public List<HoaDonDTO> LayDanhSachHoaDon(string ngay)
        {
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            List<HoaDonDTO> KQ = new List<HoaDonDTO>();
            string sql = "";
            if (ngay != "")
            {
                sql = "select * from HoaDon where convert(varchar, GioVao, 101) = '" + ngay + "'";
            }
            else
            {
                sql = "select * from HoaDon";
            }
            SqlDataAdapter dtadp = new SqlDataAdapter(sql, conn);
            try
            {
                DataTable dt = new DataTable();
                dtadp.Fill(dt);
                HoaDonDTO cn;
                foreach (DataRow dtrow in dt.Rows)
                {
                    cn = new HoaDonDTO();
                    cn.MaHD = int.Parse(dtrow["MaHD"].ToString());
                    cn.ThanhTien = float.Parse(dtrow["ThanhTien"].ToString());
                    cn.GioVao = dtrow["GioVao"].ToString();
                    cn.GioRa = dtrow["GioRa"].ToString();
                    cn.TinhTrangHD = dtrow["TinhTrangHD"].ToString();
                    cn.TenUser = dtrow["TenUser"].ToString();
                    KQ.Add(cn);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            conn.Close();
            return KQ;
        }

        public int DaThanhToanHoaDon(string maban)
        {
            int flag = 0;
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            string sql = "Update HoaDon set TinhTrangHD=N'Đã tính tiền' from ChiTietHoaDon,HoaDon ";
            string dk = " where ChiTietHoaDon.MaBan='" + maban + "' and ChiTietHoaDon.MaHD=HoaDon.MaHD and HoaDon.TinhTrangHD=N'Chưa tính tiền'";
            sql += dk;
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandText = sql;
            int kq = cmd.ExecuteNonQuery();
            if (kq > 0)
                flag = 1;
            else
                flag = 0;
            sql = "Update Ban set TrangThai=N'Trống' where MaBan=N'" + maban + "'";
            cmd = new SqlCommand(sql, conn);
            cmd.CommandText = sql;
            kq = cmd.ExecuteNonQuery();
            conn.Close();
            if (kq >= 0)
                flag = 1;
            else
                flag = 0;
            return flag;
        }

        public int LayMaHDTheoBanChuaTinhTien(string maban)
        {
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            string sql = "Select HD.MaHD from ChiTietHoaDon cthd,HoaDon HD where MaBan='" + maban + "' and cthd.MaHD=HD.MaHD and tinhtranghd=N'Chưa tính tiền'";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandText = sql;
            int kq;
            try
            {
                kq = int.Parse(cmd.ExecuteScalar().ToString());
            }
            catch
            {
                conn.Close();
                return 0;
            }
            conn.Close();
            return kq;
        }
        
    }
}
